using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using CloudApiServer.Model;
using MySql.Data.MySqlClient;

namespace CloudApiServer.Services;
/// <summary>
/// 医保服务管理功能服务
/// </summary>
public class FsiAdminApiService
{
    /// <summary>
    /// 新增人员信息
    /// </summary>
    /// <param name="fsiRequest">人员信息入参</param>
    /// <returns>成功状态</returns>
    /// <exception cref="Exception"></exception>
    public async Task<(int, object)> InsertPsnInfo(FsiHeader<outputQueryPsnInfo> fsiRequest)
    {
        try
        {
            List<Insuinfo> Insuinfolist = new List<Insuinfo>();
            List<Idetinfo> Idetinfolist = new List<Idetinfo>();
            string cert_no = fsiRequest.output.baseinfo.certno;
            Insuinfolist = fsiRequest.output.insuinfo;
            Idetinfolist = fsiRequest.output.idetinfo;
            if (string.IsNullOrEmpty(cert_no))
            {
                return (100, "参数校验失败：未传入人员身份信息cert_no！");
            }

            if (Insuinfolist.Count == 0)
            {
                return (100, "参数校验失败：入的参保信息Insuinfo列表为空！");
            }

            if (Insuinfolist.Count > 1)
            {
                return (100, "参数校验失败：传入的参保信息Insuinfo列表存在多条数据，服务端暂只支持插入一条数据，请挑选处理入参后重试！");
            }

            if (Idetinfolist.Count > 1)
            {
                return (100, "参数校验失败：传入的身份信息Idetinfo列表存在多条数据，服务端暂只支持插入一条数据，请挑选处理入参后重试！");
            }

            string connectionString =
                $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string query = "select  * From fsi_PsnInfo where certno=@certno";
            var parameter = new List<MySqlParameter>
            {
                new MySqlParameter("@certno", cert_no),
            };
            DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);

            if (dt.Rows.Count >= 1)
            {
                return (100, $"参数校验失败：插入人员信息表失败！{cert_no}已有{dt.Rows.Count}条相关数据！无需再次插入！");
            }

            query =
                "INSERT INTO `fsi_psninfo` (`certno`, `psn_no`, `gend`, `exp_content`, `brdy`, `naty`, `psn_cert_type`" +
                ", `psn_name`,`psn_mgtcode`, `insuplc_admdvs`, `psn_insu_date`, `cvlserv_flag`, `balc`, `emp_name`, `psn_type`, `psn_insu_stas`," +
                " `insutype`, `paus_insu_date`,`psn_idet_type`,`psn_type_lv`,`memo`,`begntime`,`endtime`) VALUES (@certno, @psn_no, @gend, @exp_content, @brdy, @naty,@psn_cert_type, @psn_name," +
                " @psn_mgtcode,@insuplc_admdvs, @psn_insu_date, @cvlserv_flag,@balc, @emp_name, @psn_type, @psn_insu_stas, @insutype, @paus_insu_date" +
                ", @psn_idet_type, @psn_type_lv, @memo, @begntime, @endtime)";
            parameter.Add(new MySqlParameter("@psn_no", fsiRequest.output.baseinfo.psn_no));
            parameter.Add(new MySqlParameter("@gend", fsiRequest.output.baseinfo.gend));
            parameter.Add(new MySqlParameter("@exp_content", fsiRequest.output.baseinfo.exp_content));
            parameter.Add(new MySqlParameter("@brdy", fsiRequest.output.baseinfo.brdy));
            parameter.Add(new MySqlParameter("@naty", fsiRequest.output.baseinfo.naty));
            parameter.Add(new MySqlParameter("@psn_cert_type", fsiRequest.output.baseinfo.psn_cert_type));
            parameter.Add(new MySqlParameter("@psn_name", fsiRequest.output.baseinfo.psn_name));
            parameter.Add(new MySqlParameter("@psn_mgtcode", fsiRequest.output.baseinfo.psn_mgtcode));
            parameter.Add(new MySqlParameter("@insuplc_admdvs", Insuinfolist[0].insuplc_admdvs));
            parameter.Add(new MySqlParameter("@psn_insu_date", Insuinfolist[0].psn_insu_date));
            parameter.Add(new MySqlParameter("@cvlserv_flag", Insuinfolist[0].cvlserv_flag));
            parameter.Add(new MySqlParameter("@balc", Insuinfolist[0].balc));
            parameter.Add(new MySqlParameter("@emp_name", Insuinfolist[0].emp_name));
            parameter.Add(new MySqlParameter("@psn_type", Insuinfolist[0].psn_type));
            parameter.Add(new MySqlParameter("@psn_insu_stas", Insuinfolist[0].psn_insu_stas));
            parameter.Add(new MySqlParameter("@insutype", Insuinfolist[0].insutype));
            parameter.Add(new MySqlParameter("@psn_idet_type", (object)Idetinfolist[0]?.psn_idet_type ?? DBNull.Value));
            if (Idetinfolist.Count == 0)
            {
                parameter.Add(new MySqlParameter("@psn_idet_type", ""));
                parameter.Add(new MySqlParameter("@psn_type_lv", ""));
                parameter.Add(new MySqlParameter("@memo", ""));
                parameter.Add(new MySqlParameter("@begntime", ""));
                parameter.Add(new MySqlParameter("@endtime", ""));
            }
            else
            {
                parameter.Add(new MySqlParameter("@psn_idet_type", Idetinfolist[0].psn_idet_type));
                parameter.Add(new MySqlParameter("@psn_type_lv", Idetinfolist[0].psn_type_lv));
                parameter.Add(new MySqlParameter("@memo", Idetinfolist[0].memo));
                parameter.Add(new MySqlParameter("@begntime", Idetinfolist[0].begntime));
                parameter.Add(new MySqlParameter("@endtime", Idetinfolist[0].endtime));
            }

            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
            return (0, $"新增人员信息成功！");
        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.InsertPsnInfo]新增人员信息发生异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }
    /// <summary>
    /// 删除人员信息
    /// </summary>
    /// <param name="fsiRequest">删除人员信息入参</param>
    /// <returns>成功状态</returns>
    /// <exception cref="Exception"></exception>
    public async Task<(int,object)> DeletePsnInfo(DeletePsnInfoInput fsiRequest )
    {
        try
        {
            string cert_no = fsiRequest.certno;
            if (string.IsNullOrEmpty(cert_no))
            {
                return (100, $"参数校验失败：参数校验失败：传入的人员身份证号为空！");
            }   
            
            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string query = "select  * From fsi_PsnInfo where certno=@certno";
            var parameter = new List<MySqlParameter>
            {
                new MySqlParameter("@certno", cert_no),
            };
            DataTable dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
            
            if (dt.Rows.Count < 1)
            {
                return (100, $"参数校验失败：删除医保人员信息记录失败！传入的人员身份证号{cert_no}查询到0条相关数据！无需删除！");
            }

            if (dt.Rows.Count > 1)
            {
                return (100, $"参数校验失败：删除医保人员信息记录失败！传入的人员身份证号{cert_no}查询到{dt.Rows.Count}条相关数据！请联系管理员排查人员信息表数据是否异常！");
            }
            query = "delete from fsi_PsnInfo where certno=@certno";
            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
            return (0, $"删除人员信息成功！");

        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.DeletePsnInfo]删除人员信息发生异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }

    /// <summary>
    /// 新增事前事中违规商品控制
    /// </summary>
    /// <param name="fsiRequest"></param>
    /// <returns></returns>
    public async Task<(int, object)> InsertRiskconserviceProductInfo(InsertRiskconserviceProductInfoInput fsiRequest)
    {
        try
        {
            if (string.IsNullOrEmpty(fsiRequest.med_list_codg)||string.IsNullOrEmpty(fsiRequest.rule_name)|| string.IsNullOrEmpty(fsiRequest.vola_cont) || string.IsNullOrEmpty(fsiRequest.vola_evid))
            {
                return (100, $"参数校验失败：有必传参数为空!");
            }

            var rule_name_list = new List<string>
            {
                "参保人年龄超出儿童年龄限制", "就诊人年龄超出儿童年龄限制", "与限定性别不符"
            };
            if (!rule_name_list.Contains(fsiRequest.rule_name))
            {
                return (100, $"参数校验失败：rule_name参数值有误！可选值有：参保人年龄超出儿童年龄限制，就诊人年龄超出儿童年龄限制，与限定性别不符");
            }

            if (fsiRequest.rule_name == "与限定性别不符" && string.IsNullOrEmpty(fsiRequest.compliance_condition_text))
            {
                return (100, $"参数校验失败：rule_name参数为与限定性别不符时，compliance_condition_text参数为必传参数！");
            }
            if ((fsiRequest.rule_name == "参保人年龄超出儿童年龄限制" || fsiRequest.rule_name == "就诊人年龄超出儿童年龄限制" ) && (fsiRequest.compliance_condition_num==null || fsiRequest.compliance_condition_num==0))
            {
                return (100, $"参数校验失败：rule_name参数为参保人年龄超出儿童年龄限制时，compliance_condition_num参数为必传参数！");
            }
            
            string connectionString =
                $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string query = "INSERT INTO `fsi_riskconserviceproduct` (`ID`, `med_list_codg`, `rule_id`, " +
                              "`rule_name`, `vola_cont`, `sev_deg`, `vola_evid`, `vola_bhvr_type`,product_name,compliance_condition_num,compliance_condition_text) VALUES (null, @med_list_codg, " +
                              "@rule_id, @rule_name, @vola_cont, @sev_deg,@vola_evid, @vola_bhvr_type,@product_name,@compliance_condition_num,@compliance_condition_text)";
            var parameter = new List<MySqlParameter>
            {
                new MySqlParameter("@med_list_codg", fsiRequest.med_list_codg),
                new MySqlParameter("@rule_id", fsiRequest.rule_id ?? DateTime.Now.ToString("yyyyMMddHHmmssfff")),
                new MySqlParameter("@rule_name", fsiRequest.rule_name),
                new MySqlParameter("@vola_cont", fsiRequest.vola_cont),
                new MySqlParameter("@sev_deg", fsiRequest.sev_deg ?? "1"),
                new MySqlParameter("@vola_evid", fsiRequest.vola_evid),
                new MySqlParameter("@vola_bhvr_type", fsiRequest.vola_bhvr_type ?? "2"),
                new MySqlParameter("@product_name", fsiRequest.product_name),
                new MySqlParameter("@compliance_condition_num", fsiRequest.compliance_condition_num),
                new MySqlParameter("@compliance_condition_text", fsiRequest.compliance_condition_text)
            };
            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
            return (0, $"新增事前事中违规商品成功！");
        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.InsertRiskconserviceProductInfo]新增事前事中违规商品发生异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="fsiRequest"></param>
    /// <returns></returns>
    public async Task<(int, object)> DeleteRiskconserviceProductInfo(InsertRiskconserviceProductInfoInput fsiRequest)
    {
        try
        {
            if (string.IsNullOrEmpty(fsiRequest.med_list_codg))
            {
                return (100, $"参数校验失败：传入的国家编码为空!");
            } 
            string connectionString =
                $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string query ="select * from fsi_riskconserviceproduct where med_list_codg=@med_list_codg";
            var parameter = new List<MySqlParameter>
            {
                new MySqlParameter("@med_list_codg", fsiRequest.med_list_codg),
            };
            var dt = await MySqlHelper.ExecuteQueryAsync(connectionString, query, parameter);
            if (dt.Rows.Count == 0)
            { 
                return (100, $"参数校验失败：删除事前事中违规商品控制记录失败！传入的国家编码{fsiRequest.med_list_codg}查询到{dt.Rows.Count}条相关数据！无需删除！");
            }
            query = "delete from fsi_riskconserviceproduct where med_list_codg=@med_list_codg";
            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
            return (0, $"删除事前事中违规商品成功！");
        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.DeleteRiskconserviceProductInfo]删除事前事中违规商品发生异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }
    
    /// <summary>
    /// 新增人员待遇享受检查
    /// </summary>
    /// <param name="fsiRequest">人员待遇享受检查入参</param>
    /// <returns>状态码/输出信息</returns>
    /// <exception cref="Exception"></exception>
    public async Task<(int,object)> InsertPsnPriorityInfo(FsiHeader<OutputQueryPsnPriorityInfo> fsiRequest)
    {
        try
        {
            List<OutputQueryPsnPriorityInfoTrtinfoList> list = new List<OutputQueryPsnPriorityInfoTrtinfoList>();
            list = fsiRequest.output.trtinfo;
            if (list.Count == 0)
            {
                return (100, "参数校验失败：传入的人员待遇享受检查数组为空，请检查入参！");
            }

            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string error = null;
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();
                using (var transaction = conn.BeginTransaction())
                {
                    foreach (var item in list)
                    {
                        try
                        {
                            var parameters = new List<MySqlParameter>
                            {
                                new MySqlParameter("@psn_no", item.psn_no),
                                new MySqlParameter("@trt_chk_type", item.trt_chk_type),
                                new MySqlParameter("@fund_pay_type", item.fund_pay_type),
                                new MySqlParameter("@trt_enjymnt_flag", item.trt_enjymnt_flag),
                                new MySqlParameter("@begndate", item.begndate),
                                new MySqlParameter("@enddate", item.enddate),
                                new MySqlParameter("@exp_content", item.exp_content),
                            };
                            string query =
                                "select count(*) from fsi_querypsnpriorityinfo where psn_no = @psn_no and fund_pay_type=@fund_pay_type";
                            int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, query, parameters, transaction);
                            if (count == 0)
                            {
                                query = "insert into fsi_querypsnpriorityinfo (id, psn_no, trt_chk_type, fund_pay_type, trt_enjymnt_flag, begndate, enddate,exp_content, create_time, update_time) values (null,@psn_no, @trt_chk_type, @fund_pay_type, @trt_enjymnt_flag, @begndate, @enddate,@exp_content, now(), now());";
                            }
                            else
                            {
                                query =
                                    "  update fsi_querypsnpriorityinfo set trt_chk_type = @trt_chk_type,trt_enjymnt_flag = @trt_enjymnt_flag,begndate=@begndate,enddate=@enddate,exp_content=@exp_content, update_time =now()  where psn_no = @psn_no and fund_pay_type=@fund_pay_type";
                            }
                            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameters, transaction);
                        }
                        catch (Exception ex)
                        {
                            error +=
                                $"人员编号【{item.psn_no}】基金支付类型【{item.fund_pay_type}】新增/更新出错：{ex.Message};";
                        }
                    }

                    if (!string.IsNullOrEmpty(error))
                    {
                        transaction.Rollback();
    
                        return (100, $"尝试新增或更新人员待遇享受检查失败，错误内容：{error}");
                    }

                    transaction.Commit();
                    return (0, $"新增人员待遇享受检查成功！");
                }
            }
        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.InsertPsnPriorityInfo]新增人员待遇享受检查发生异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }

    /// <summary>
    /// 删除人员待遇享受检查
    /// </summary>
    /// <param name="fsiRequest">删除人员待遇享受检查入参</param>
    /// <returns>状态码/输出信息</returns>
    /// <exception cref="Exception"></exception>
    public async Task<(int,object)> DeletePsnPriorityInfo(DeletePsnPriorityInfoInput fsiRequest )
    {
        try
        {
            string psn_no = fsiRequest.psn_no;
            string fund_pay_type = fsiRequest.fund_pay_type;
            if (string.IsNullOrEmpty(psn_no))
            {
                return (100, "参数校验失败：传入的人员编号为空!");
            }

            string connectionString =
                $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string query = "select count(*) From fsi_querypsnpriorityinfo where psn_no=@psn_no ";
            if (!string.IsNullOrEmpty(fund_pay_type))
            {
                query += " and fund_pay_type=@fund_pay_type";
            }
            var parameter = new List<MySqlParameter>
            {
                new MySqlParameter("@psn_no", psn_no),
                new MySqlParameter("@fund_pay_type",fund_pay_type)
            };
            int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, query, parameter);

            if (count == 0)
            { 
                return (100, $"业务参数校验失败：删除医保人员待遇享受检查记录失败！传入的人员编号【{psn_no}】基金支付类型【{fund_pay_type}】查询到0条相关数据！无需删除！");
            }
            query = "delete from fsi_querypsnpriorityinfo where psn_no=@psn_no ";
            if (!string.IsNullOrEmpty(fund_pay_type))
            {
                query += " and fund_pay_type=@fund_pay_type";
            }
            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
            return (0,$"删除人员待遇享受检查数成功!");

        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.DeletePsnPriorityInfo]删除人员待遇享受检查发生异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }
    
    
    /// <summary>
    /// 新增两定机构信息
    /// </summary>
    /// <param name="fsiRequest">人员待遇享受检查入参</param>
    /// <returns>状态码/输出信息</returns>
    /// <exception cref="Exception"></exception>
    public async Task<(int,object)> InsertFixmedins(FsiHeader<OutputQueryFixMedIns> fsiRequest)
    {
        try
        {
            List<OutputQueryFixMedInsedinsinfoList> list = new List<OutputQueryFixMedInsedinsinfoList>();
            list = fsiRequest.output.medinsinfo;
            if (list.Count == 0)
            {
                return (100, "参数校验失败：传入的两定机构信息列表为空，请检查入参！");
            }

            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string error = null;
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();
                using (var transaction = conn.BeginTransaction())
                {
                    foreach (var item in list)
                    {
                        try
                        {
                            var parameters = new List<MySqlParameter>
                            {
                                new MySqlParameter("@fixmedins_code", item.fixmedins_code),
                                new MySqlParameter("@uscc", item.uscc),
                                new MySqlParameter("@exp_content", item.exp_content),
                                new MySqlParameter("@fixmedins_name", item.fixmedins_name),
                                new MySqlParameter("@fixmedins_type", item.fixmedins_type),
                                new MySqlParameter("@hosp_lv", item.hosp_lv),
                            };
                            string query =
                                "select count(*) from fsi_queryfixmedins where fixmedins_code = @fixmedins_code";
                            int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, query, parameters, transaction);
                            if (count == 0)
                            {
                                query = "insert into fsi_queryfixmedins (id, fixmedins_code, fixmedins_name, uscc, fixmedins_type, hosp_lv,exp_content, create_time, update_time) values (null,@fixmedins_code, @fixmedins_name, @uscc, @fixmedins_type, @hosp_lv, @exp_content, now(), now());";
                            }
                            else
                            {
                                query =
                                    "  update fsi_queryfixmedins set fixmedins_code = @fixmedins_code,fixmedins_name = @fixmedins_name,uscc=@uscc,fixmedins_type=@fixmedins_type,hosp_lv=@hosp_lv,exp_content=@exp_content, update_time =now()  where fixmedins_code = @fixmedins_code ";
                            }
                            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameters, transaction);
                        }
                        catch (Exception ex)
                        {
                            error +=
                                $"机构代码【{item.fixmedins_code}】j机构名称【{item.fixmedins_name}】新增/更新出错：{ex.Message};";
                        }
                    }

                    if (!string.IsNullOrEmpty(error))
                    {
                        transaction.Rollback();
    
                        return (100, $"尝试新增或更新两定机构信息失败，错误内容：{error}");
                    }

                    transaction.Commit();
                    return (0, $"新增两定机构信息成功！");
                }
            }
        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.InsertFixmedins]新增两定机构信息异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }
    
    /// <summary>
    /// 删除两定机构信息
    /// </summary>
    /// <param name="fsiRequest">删除人员待遇享受检查入参</param>
    /// <returns>状态码/输出信息</returns>
    /// <exception cref="Exception"></exception>
    public async Task<(int,object)> DeleteFixmedins(DeleteFixmedinsInput fsiRequest )
    {
        try
        {
            string fixmedins_code = fsiRequest.fixmedins_code;
            if (string.IsNullOrEmpty(fixmedins_code))
            {
                return (100, "参数校验失败：传入的机构代码为空!");
            }

            string connectionString =
                $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string query = "select count(*) From fsi_queryfixmedins where fixmedins_code=@fixmedins_code ";
            var parameter = new List<MySqlParameter>
            {
                new MySqlParameter("@fixmedins_code", fixmedins_code),
            };
            int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, query, parameter);

            if (count == 0)
            { 
                return (100, $"业务参数校验失败：删除两定机构信息失败！传入的机构代码【{fixmedins_code}】查询到0条相关数据！无需删除！");
            }
            query = "delete from fsi_queryfixmedins where fixmedins_code=@fixmedins_code ";
            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
            return (0,$"删除两定机构信息数据成功!");

        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.DeleteFixmedins]删除两定机构信息异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }
    
    
    /// <summary>
    /// 新增人员慢特病备案
    /// </summary>
    /// <param name="fsiRequest">人员慢特病备案入参</param>
    /// <param name="psn_no">人员编号</param>
    /// <returns>状态码/输出信息</returns>
    /// <exception cref="Exception"></exception>
    public async Task<(int,object)> InsertPsnOpspReg(FsiHeader<OutputQueryPsnOpspReg> fsiRequest,string psn_no)
    {
        try
        {
            List<FeedetailQueryPsnOpspReg> list = new List<FeedetailQueryPsnOpspReg>();
            list = fsiRequest.output.feedetail;
            if (list.Count == 0 || string.IsNullOrEmpty(psn_no))
            {
                return (100, "参数校验失败：传入人员慢特病备案为空或人员编号为空，请检查入参！");
            }

            string connectionString = $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string error = null;
            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();
                using (var transaction = conn.BeginTransaction())
                {
                    foreach (var item in list)
                    {
                        try
                        {
                            var parameters = new List<MySqlParameter>
                            {
                                new MySqlParameter("@begndate", item.begndate),
                                new MySqlParameter("@enddate", item.enddate),
                                new MySqlParameter("@exp_content", item.exp_content),
                                new MySqlParameter("@opsp_dise_name", item.opsp_dise_name),
                                new MySqlParameter("@opsp_dise_code", item.opsp_dise_code),
                                new MySqlParameter("@psn_no", psn_no),
                            };
                            
                            string query = "select count(*) from fsi_psninfo where psn_no = @psn_no";
                            int psninfocount = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, query, parameters, transaction);
                            if (psninfocount == 0)
                            {
                                return (100, $"参数校验失败：根据传入人员编号【{psn_no}】查询到0条人员信息，请检查入参！");
                            }
                            query = "select count(*) from fsi_querypsnopspreg where psn_no = @psn_no";
                            int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, query, parameters, transaction);
                            if (count == 0)
                            {
                                query =
                                    "insert into fsi_querypsnopspreg (psn_no, opsp_dise_code, opsp_dise_name, begndate, enddate, exp_content, create_time,update_time)  values (@psn_no, @opsp_dise_code, @opsp_dise_name, @begndate, @enddate, @exp_content, now(),now())";
                            }
                            else
                            {
                                query =
                                    "  update fsi_querypsnopspreg set opsp_dise_code = @opsp_dise_code,opsp_dise_name = @opsp_dise_name,begndate=@begndate,enddate=@enddate,exp_content=@exp_content, update_time =now()  where psn_no = @psn_no ";
                            }
                            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameters, transaction);
                        }
                        catch (Exception ex)
                        {
                            error +=
                                $"人员编号【{psn_no}】慢特病种代码【{item.opsp_dise_code}】慢特病种名称【{item.opsp_dise_name}】新增/更新出错：{ex.Message};";
                        }
                    }

                    if (!string.IsNullOrEmpty(error))
                    {
                        transaction.Rollback();
    
                        return (100, $"尝试新增人员慢特病备案失败，错误内容：{error}");
                    }

                    transaction.Commit();
                    return (0, $"新增人员慢特病备案成功！");
                }
            }
        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.InsertPsnOpspReg]新增人员慢特病备案异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }
    
    /// <summary>
    /// 删除人员慢特病备案
    /// </summary>
    /// <param name="fsiRequest">删除人员慢特病备案入参</param>
    /// <returns>状态码/输出信息</returns>
    /// <exception cref="Exception"></exception>
    public async Task<(int,object)> DeletePsnOpspReg(DeletePsnOpspRegInput fsiRequest )
    {
        try
        {
            string psn_no = fsiRequest.psn_no;
            if (string.IsNullOrEmpty(psn_no))
            {
                return (100, "参数校验失败：传入的人员编号为空!");
            }

            string connectionString =
                $"Database={ApiDatainfo.mysqlDatabase};Data Source={ApiDatainfo.mysqlUrl};Port={ApiDatainfo.mysqlPost};UserId={ApiDatainfo.mysqlUser};Password={ApiDatainfo.mysqlPwd};Charset=utf8mb4;TreatTinyAsBoolean=false;Allow User Variables=True";
            string query = "select count(*) From fsi_querypsnopspreg where psn_no=@psn_no ";
            var parameter = new List<MySqlParameter>
            {
                new MySqlParameter("@psn_no", psn_no),
            };
            int count = await MySqlHelper.ExecuteScalarAsync<int>(connectionString, query, parameter);

            if (count == 0)
            { 
                return (100, $"业务参数校验失败：删除人员慢特病备案失败！传入的人员编号【{psn_no}】查询到0条相关数据！无需删除！");
            }
            query = "delete from fsi_querypsnopspreg where psn_no=@psn_no ";
            await MySqlHelper.ExecuteNonQueryAsync(connectionString, query, parameter);
            return (0,$"删除人员慢特病备案成功!");

        }
        catch (Exception ex)
        {
            LogHelper.Error($"[FsiAdminApiService.DeletePsnOpspReg]删除人员慢特病备案异常，错误内容：{ex.Message} 异常堆栈{ex.StackTrace}");
            throw;
        }
    }

}